package binbase.web.gui

import core.QueryService
import grails.converters.JSON
import binbase.web.core.BBBin
import groovy.sql.Sql
import org.codehaus.groovy.grails.web.servlet.mvc.GrailsParameterMap

import static binbase.web.QueryUtil.*
import org.codehaus.groovy.grails.commons.ConfigurationHolder
/**
 * used to query organ and species information and to use datatables to build the interface
 * it uses direct sql so no gorm here
 */
class BBOrganAndSpeciesQueryController {

    def dataSource

    def exportService

    QueryService queryService

    def index = { }

    /**
     * generates a json string containing all bin information for the given organId
     */
    def ajaxBinsByOrganAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho
        dataToRender.iTotalRecords = queryService.binsForOrgan(params.organId).size()
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        if (params."sSearch" == null || params."sSearch".toString().size() == 0) {

            /**
             * query string for this, will be modified with limiting and sorting options
             */
            def query = new StringBuffer("""


select cast(samples as float )/samples_total * 100  as relative,samples_total, samples as found_in_samples,name,binbase_bin_id,organ,a.retention_index,a.unique_mass from bbbin a,
(
select f.bin_id ,count(e.id) as samples from bbexperiment_class d,bbexperiment_sample e, bbspectra f where d.organ_id = ${params.organId.toString().toInteger()} and e.experiment_class_id = d.id and f.sample_id = e.id group by f.bin_id
)
b,
(
select count(e.id) as samples_total from bbexperiment_class d,bbexperiment_sample e where d.organ_id = ${params.organId.toString().toInteger()} and e.experiment_class_id = d.id
)
c,
(
select a.name as organ from bborgan a  where a.id = ${params.organId.toString().toInteger()}
)
d

where a.id = b.bin_id


        """)



            buildQuery(params, query, [
                    "relative",
                    "found_in_samples",
                    "samples_total",
                    "name",
                    "binbase_bin_id",
                    "retention_index",
                    "unique_mass",
                    "organ"
            ])

            sql.eachRow(query.toString(), { def bin ->

                dataToRender.aaData << [
                        bin.relative,
                        bin.found_in_samples,
                        bin.samples_total,
                        bin.name,
                        bin.binbase_bin_id,
                        bin.retention_index,
                        bin.unique_mass,
                        bin.organ
                ]

            })
        }
        else {

            /**
             * query string for this, will be modified with limiting and sorting options
             */
            def query = new StringBuffer("""


select cast(samples as float )/samples_total * 100  as relative,samples_total, samples as found_in_samples,name,binbase_bin_id,organ,a.retention_index,a.unique_mass from bbbin a,
(
select f.bin_id ,count(e.id) as samples from bbexperiment_class d,bbexperiment_sample e, bbspectra f where d.organ_id = ${params.organId.toString().toInteger()} and e.experiment_class_id = d.id and f.sample_id = e.id group by f.bin_id
)
b,
(
select count(e.id) as samples_total from bbexperiment_class d,bbexperiment_sample e where d.organ_id = ${params.organId.toString().toInteger()} and e.experiment_class_id = d.id
)
c,
(
select a.name as organ from bborgan a  where a.id = ${params.organId.toString().toInteger()}
)
d

where a.id = b.bin_id and lower(name) like ?


        """)



            buildQuery(params, query, [
                    "relative",
                    "found_in_samples",
                    "samples_total",
                    "name",
                    "binbase_bin_id",
                    "retention_index",
                    "unique_mass",
                    "organ"
            ])

            sql.eachRow(query.toString(), ["%${params."sSearch".toString().toLowerCase()}%".toString()], { def bin ->

                dataToRender.aaData << [
                        bin.relative,
                        bin.found_in_samples,
                        bin.samples_total,
                        bin.name,
                        bin.binbase_bin_id,
                        bin.retention_index,
                        bin.unique_mass,
                        bin.organ
                ]

            })
        }

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=binsByOrgan.xls")

            def exports = []

            def fields = ["relative", "absolute", "total", "name", "binId", "retentionIndex", "uniqueMass", "organ"]
            def labels = ["relative ": "Relative %", "absolute": "Absolute", "total": "Total Sample count", "name": "name", "binId": "Bin Id", "retentionIndex": "Retention Index", "uniqueMass": "Unique Mass", "organ": "Organ",]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.relative = t[0]
                o.metaClass.absolute = t[1]
                o.metaClass.total = t[2]
                o.metaClass.name = t[3]
                o.metaClass.binId = t[4]
                o.metaClass.retentionIndex = t[5]
                o.metaClass.uniqueMass = t[6]
                o.metaClass.organ = t[7]


                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }

    }

    /**
     * generates a json string containning all bins for the given organ and species id
     */
    def ajaxBinsByOrganAndSpeciesAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho
        dataToRender.iTotalRecords = queryService.binsForOrganAndSpecies(params.organId, params.speciesId).size()
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        if (params."sSearch" == null || params."sSearch".toString().size() == 0) {

            /**
             * query string for this, will be modified with limiting and sorting options
             */
            def query = new StringBuffer("""


select cast(samples as float )/samples_total * 100  as relative,samples_total, samples as found_in_samples,name,binbase_bin_id,organ,species,a.retention_index,a.unique_mass from bbbin a,
(
select f.bin_id ,count(e.id) as samples from bbexperiment_class d,bbexperiment_sample e, bbspectra f where d.organ_id = ${params.organId.toString().toInteger()} and d.species_id = ${params.speciesId.toString().toInteger()}  and e.experiment_class_id = d.id and f.sample_id = e.id group by f.bin_id
)
b,
(
select count(e.id) as samples_total from bbexperiment_class d,bbexperiment_sample e where d.organ_id = ${params.organId.toString().toInteger()} and d.species_id = ${params.speciesId.toString().toInteger()} and e.experiment_class_id = d.id
)
c,
(
select a.name as organ,b.name as species from bborgan a, bbspecies b, bbspecies_organs c where a.id = ${params.organId.toString().toInteger()}  and b.id = ${params.speciesId.toString().toInteger()}  and a.id = c.bborgan_id and c.bbspecies_id = b.id
)
d

where a.id = b.bin_id


        """)

            buildQuery(params, query, [
                    "relative",
                    "found_in_samples",
                    "samples_total",
                    "name",
                    "binbase_bin_id",
                    "retention_index",
                    "unique_mass",
                    "organ",
                    "species"
            ])

            sql.eachRow(query.toString(), { def bin ->

                dataToRender.aaData << [
                        bin.relative,
                        bin.found_in_samples,
                        bin.samples_total,
                        bin.name,
                        bin.binbase_bin_id,
                        bin.retention_index,
                        bin.unique_mass,
                        bin.organ,
                        bin.species

                ]

            })
        }
        else {
            /**
             * query string for this, will be modified with limiting and sorting options
             */
            def query = new StringBuffer("""


select cast(samples as float )/samples_total * 100  as relative,samples_total, samples as found_in_samples,name,binbase_bin_id,organ,species,a.retention_index,a.unique_mass from bbbin a,
(
select f.bin_id ,count(e.id) as samples from bbexperiment_class d,bbexperiment_sample e, bbspectra f where d.organ_id = ${params.organId.toString().toInteger()} and d.species_id = ${params.speciesId.toString().toInteger()}  and e.experiment_class_id = d.id and f.sample_id = e.id group by f.bin_id
)
b,
(
select count(e.id) as samples_total from bbexperiment_class d,bbexperiment_sample e where d.organ_id = ${params.organId.toString().toInteger()} and d.species_id = ${params.speciesId.toString().toInteger()} and e.experiment_class_id = d.id
)
c,
(
select a.name as organ,b.name as species from bborgan a, bbspecies b, bbspecies_organs c where a.id = ${params.organId.toString().toInteger()}  and b.id = ${params.speciesId.toString().toInteger()}  and a.id = c.bborgan_id and c.bbspecies_id = b.id
)
d

where a.id = b.bin_id and lower(name) like ?


        """)

            buildQuery(params, query, [
                    "relative",
                    "found_in_samples",
                    "samples_total",
                    "name",
                    "binbase_bin_id",
                    "retention_index",
                    "unique_mass",
                    "organ",
                    "species"
            ])


            sql.eachRow(query.toString(), ["%${params."sSearch".toString().toLowerCase()}%".toString()], { def bin ->

                dataToRender.aaData << [
                        bin.relative,
                        bin.found_in_samples,
                        bin.samples_total,
                        bin.name,
                        bin.binbase_bin_id,
                        bin.retention_index,
                        bin.unique_mass,
                        bin.organ,
                        bin.species

                ]

            })
        }

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=binsByOrganAndSpecies.xls")

            def exports = []

            def fields = ["relative", "absolute", "total", "name", "binId", "retentionIndex", "uniqueMass", "organ", "species"]
            def labels = ["relative ": "Relative %", "absolute": "Absolute", "total": "Total Sample count", "name": "name", "binId": "Bin Id", "retentionIndex": "Retention Index", "uniqueMass": "Unique Mass", "organ": "Organ", "species": "Species"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.relative = t[0]
                o.metaClass.absolute = t[1]
                o.metaClass.total = t[2]
                o.metaClass.name = t[3]
                o.metaClass.binId = t[4]
                o.metaClass.retentionIndex = t[5]
                o.metaClass.uniqueMass = t[6]
                o.metaClass.organ = t[7]
                o.metaClass.species = t[8]



                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }
    }

    /**
     * renders all bins for the given species id
     */
    def ajaxBinsBySpeciesAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho
        dataToRender.iTotalRecords = queryService.binsForSpecies(params.speciesId).size()
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        if (params."sSearch" == null || params."sSearch".toString().size() == 0) {

            /**
             * query string for this, will be modified with limiting and sorting options
             */
            def query = new StringBuffer("""


select cast(samples as float )/samples_total * 100  as relative,samples_total, samples as found_in_samples,name,binbase_bin_id,species,a.retention_index,a.unique_mass from bbbin a,
(
select f.bin_id ,count(e.id) as samples from bbexperiment_class d,bbexperiment_sample e, bbspectra f where  d.species_id = ${params.speciesId.toString().toInteger()}  and e.experiment_class_id = d.id and f.sample_id = e.id group by f.bin_id
)
b,
(
select count(e.id) as samples_total from bbexperiment_class d,bbexperiment_sample e where d.species_id = ${params.speciesId.toString().toInteger()} and e.experiment_class_id = d.id
)
c,
(
select b.name as species from bbspecies b where  b.id = ${params.speciesId.toString().toInteger()}
)
d

where a.id = b.bin_id


        """)

            buildQuery(params, query, [
                    "relative",
                    "found_in_samples",
                    "samples_total",
                    "name",
                    "binbase_bin_id",
                    "retention_index",
                    "unique_mass",
                    "species"
            ])

            sql.eachRow(query.toString(), { def bin ->

                dataToRender.aaData << [
                        bin.relative,
                        bin.found_in_samples,
                        bin.samples_total,
                        bin.name,
                        bin.binbase_bin_id,
                        bin.retention_index,
                        bin.unique_mass,
                        bin.species

                ]

            })

        }
        else {
            def query = new StringBuffer("""


select cast(samples as float )/samples_total * 100  as relative,samples_total, samples as found_in_samples,name,binbase_bin_id,species,a.retention_index,a.unique_mass from bbbin a,
(
select f.bin_id ,count(e.id) as samples from bbexperiment_class d,bbexperiment_sample e, bbspectra f where  d.species_id = ${params.speciesId.toString().toInteger()}  and e.experiment_class_id = d.id and f.sample_id = e.id group by f.bin_id
)
b,
(
select count(e.id) as samples_total from bbexperiment_class d,bbexperiment_sample e where d.species_id = ${params.speciesId.toString().toInteger()} and e.experiment_class_id = d.id
)
c,
(
select b.name as species from bbspecies b where  b.id = ${params.speciesId.toString().toInteger()}
)
d

where a.id = b.bin_id and lower(name) like ?


        """)

            buildQuery(params, query, [
                    "relative",
                    "found_in_samples",
                    "samples_total",
                    "name",
                    "binbase_bin_id",
                    "retention_index",
                    "unique_mass",
                    "species"
            ])

            sql.eachRow(query.toString(), ["%${params."sSearch".toString().toLowerCase()}%".toString()], { def bin ->

                dataToRender.aaData << [
                        bin.relative,
                        bin.found_in_samples,
                        bin.samples_total,
                        bin.name,
                        bin.binbase_bin_id,
                        bin.retention_index,
                        bin.unique_mass,
                        bin.species

                ]

            })

        }

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=binsBySpecies.xls")

            def exports = []

            def fields = ["relative", "absolute", "total", "name", "binId", "retentionIndex", "uniqueMass", "species"]
            def labels = ["relative ": "Relative %", "absolute": "Absolute", "total": "Total Sample count", "name": "name", "binId": "Bin Id", "retentionIndex": "Retention Index", "uniqueMass": "Unique Mass", "species": "Species"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.relative = t[0]
                o.metaClass.absolute = t[1]
                o.metaClass.total = t[2]
                o.metaClass.name = t[3]
                o.metaClass.binId = t[4]
                o.metaClass.retentionIndex = t[5]
                o.metaClass.uniqueMass = t[6]
                o.metaClass.species = t[7]



                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }
    }

    /**
     * finds all the experiments for the given speciesId
     */
    def ajaxExperimentsForSpeciesAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho
        dataToRender.iTotalRecords = queryService.experimentsForSpecies(params.speciesId).size()
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        def query = new StringBuffer("""
            select id,name,title from bbexperiment a where a.id in ( select experiment_id from bbexperiment_class where species_id = ${params.speciesId as int})
        """)



        buildQuery(params, query, [
                "id",
                "name",
                "title"
        ])

        sql.eachRow(query.toString(), { def bin ->

            dataToRender.aaData << [
                    bin.id,
                    bin.name,
                    bin.title
            ]

        })

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=experiments.xls")

            def exports = []

            def fields = ["name", "title"]
            def labels = ["name": "Name", "title": "Title"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.name = t[1]
                o.metaClass.title = t[2]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }
    }

    /**
     * finds all the experiments for the given organId
     */
    def ajaxExperimentsForOrgansAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho
        dataToRender.iTotalRecords = queryService.experimentsForOrgan(params.organId).size()
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        def query = new StringBuffer("""
            select id,name,title from bbexperiment a where a.id in ( select experiment_id from bbexperiment_class where organ_id = ${params.organId as int})
        """)

        buildQuery(params, query, [
                "id",
                "name",
                "title"
        ])

        sql.eachRow(query.toString(), { def bin ->

            dataToRender.aaData << [
                    bin.id,
                    bin.name,
                    bin.title
            ]

        })

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=experiments.xls")

            def exports = []

            def fields = ["name", "title"]
            def labels = ["name": "Name", "title": "Title"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.name = t[1]
                o.metaClass.title = t[2]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }
    }

    /**
     * finds all the experiments for the given speciesId and organId
     */
    def ajaxExperimentsForSpeciesAndOrganAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho
        dataToRender.iTotalRecords = queryService.experimentsForSpeciesAndOrgan(params.speciesId, params.organId).size()
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        def query = new StringBuffer("""
            select id,name,title from bbexperiment a where a.id in ( select experiment_id from bbexperiment_class where species_id = ${params.speciesId as int} and organ_id = ${params.organId as int})
        """)

        buildQuery(params, query, [
                "id",
                "name",
                "title"
        ])

        sql.eachRow(query.toString(), { def bin ->

            dataToRender.aaData << [
                    bin.id,
                    bin.name,
                    bin.title
            ]

        })

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=experiments.xls")

            def exports = []

            def fields = ["name", "title"]
            def labels = ["name": "Name", "title": "Title"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.name = t[1]
                o.metaClass.title = t[2]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }
    }

    /**
     * all species for organs
     */
    def ajaxSpeciesByOrganAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        def query = new StringBuffer("""
                   select id ,name  from bbspecies a where a.id in ( select distinct bbspecies_id from bbspecies_organs where bborgan_id = ${params.organId as int})
               """)


        dataToRender.iTotalRecords = sql.firstRow("select count(distinct bbspecies_id) as id from bbspecies_organs where bborgan_id = ${params.organId as int} ").id
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        buildQuery(params, query, [
                "id",
                "name"
        ])

        sql.eachRow(query.toString(), { def bin ->

            dataToRender.aaData << [
                    bin.id,
                    bin.name
            ]

        })

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=species.xls")

            def exports = []

            def fields = ["name"]
            def labels = ["name": "Name"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.name = t[1]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }
    }

    /**
     * alls organs for species
     */
    def ajaxOrganBySpeciesAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        def query = new StringBuffer("""
                   select id ,name  from bborgan a where a.id in ( select distinct bborgan_id from bbspecies_organs where bbspecies_id = ${params.speciesId as int})
               """)


        dataToRender.iTotalRecords = sql.firstRow("select count(distinct bborgan_id) as id from bbspecies_organs where bbspecies_id = ${params.speciesId as int} ").id
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        buildQuery(params, query, [
                "id",
                "name"
        ])

        sql.eachRow(query.toString(), { def bin ->

            dataToRender.aaData << [
                    bin.id,
                    bin.name
            ]

        })


        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=organ.xls")

            def exports = []

            def fields = ["name"]
            def labels = ["name": "Name"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.name = t[1]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }
    }

    /**
     * alls organs for species
     */
    def ajaxListSpeciesAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        def query = new StringBuffer("""
                   select * from bbspecies
               """)


        dataToRender.iTotalRecords = sql.firstRow("select count(*) as id from bbspecies").id
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        buildQuery(params, query, [
                "id",
                "name"
        ])

        sql.eachRow(query.toString(), { def bin ->

            dataToRender.aaData << [
                    bin.id,
                    bin.name
            ]

        })

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=species.xls")

            def exports = []

            def fields = ["name"]
            def labels = ["name ": "Species Name"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.name = t[1]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }
    }

    /**
     * alls organs for species
     */
    def ajaxListOrgansAsJSON = {

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        def query = new StringBuffer("""
                   select * from bborgan
               """)


        dataToRender.iTotalRecords = sql.firstRow("select count(*) as id from bborgan").id
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        buildQuery(params, query, [
                "id",
                "name"
        ])

        sql.eachRow(query.toString(), { def bin ->

            dataToRender.aaData << [
                    bin.id,
                    bin.name
            ]

        })

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=organs.xls")

            def exports = []

            def fields = ["name"]
            def labels = ["name ": "Organ Name"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.name = t[1]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }

    }


}